package daoJDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import connection.ConnectionDB;
import entityJDBC.Position;
import entityJDBC.PositionDescription;
import entityJDBC.PositionStatus;
import entityJDBC.User;
public class JDBCPositionDAO /*implements IPositionDAO*/ {

	ConnectionDB conDb = ConnectionDB.getInstance();
	JDBCUserDao userDAO= new JDBCUserDao();
	/*@Override*/
	public List<Position> findAll() {
		List<Position> positionList=new ArrayList<Position>();
		Connection con = conDb.getConnection();
		String command = "Select * from position inner join positiondescription on position.idPositionDescription =positiondescription.idPositionDescription "
				+ "inner join positionstatus on position.idPositionStatus=positionstatus.idPositionStatus inner join user on position.idUser=user.idUser";
		PreparedStatement stmt;
		try {
			stmt = con.prepareStatement(command);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				int idPosition=rs.getInt("IDPOSITION");
				String name = rs.getString("NAME");
				int nrPers = rs.getInt("NRPERSONS");	
				User user = userDAO.findById(rs.getInt("IDUSER"));
				int idPosDesc=rs.getInt("IDPOSITIONDESCRIPTION");
				int idDepartment=rs.getInt("iDDEPARTMENT");
				String req = rs.getString("REQUIREMENTS");
				String responsabilities =rs.getString("RESPONSABILITIES");
				String project=rs.getString("PROJECT");
				PositionDescription posDesc =new PositionDescription(idPosDesc, idDepartment, req, responsabilities, project);
				PositionStatus posStatus = new PositionStatus(rs.getInt("IDPOSITIONDESCRIPTION"), rs.getString("IDPOSITIONSTATUS"));
				Position pos = new Position(idPosition, name, nrPers, user, posDesc,posStatus);
				positionList.add(pos); 
			}
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			conDb.disconectDatabase();
		}
		return positionList;
		
		
	}

	/*@Override*/
	public Position findById(int id) {
		Connection con = conDb.getConnection();
		String command = "Select * from position inner join positionDescription on position.idPositionDescription=positionDescription.idPositionDescription  where idPosition = ?";
		PreparedStatement stmt;
		Position pos = null;
		try {
			stmt = con.prepareStatement(command);
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				String name = rs.getString("NAME");
				int nrPers = rs.getInt("NRPERSONS");
				User user = userDAO.findById(rs.getInt("IDUSER"));
				int idPosDesc=rs.getInt("IDPOSITIONDESCRIPTION");
				int idDepartment=rs.getInt("iDDEPARTMENT");
				String req = rs.getString("REQUIREMENTS");
				String responsabilities =rs.getString("RESPONSABILITIES");
				String project=rs.getString("PROJECT");
				PositionDescription posDesc =new PositionDescription(idPosDesc, idDepartment, req, responsabilities, project);
				PositionStatus posStatus = new PositionStatus(rs.getInt("IDPOSITIONDESCRIPTION"), rs.getString("IDPOSITIONSTATUS"));
				pos = new Position(id, name, nrPers, user, posDesc,posStatus);
				
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			conDb.disconectDatabase();
		}
		return pos;
	}

	/*@Override*/
	public void insert(Position pos) {
		Connection con = conDb.getConnection();
		String command = "Insert into position (idPosition,name,idPositionStatus,nrPersons,idUser,idPositionDescription) values (?,?,?,?,?,?)";
		PreparedStatement stmt;
		try {
			stmt = con.prepareStatement(command);
			stmt.setInt(1, pos.getIdPosition());
			stmt.setString(2, pos.getName());
			stmt.setInt(3, pos.getNrPersons());
			stmt.setInt(4, pos.getUser().getIdUser());
			stmt.setInt(5, pos.getPositionDescription().getIdpositiondescription());
			stmt.setInt(6, pos.getPositionStatus().getIdPositionStatus());
			stmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			conDb.disconectDatabase();
		}

	}

	/*@Override*/
	public void delete(Position pos) {
		Connection con = conDb.getConnection();
		String command = "Delete from position where idPosition = ? ";
		PreparedStatement stmt;
		try {
			stmt = con.prepareStatement(command);
			stmt.setInt(1, pos.getIdPosition());
			stmt.executeUpdate();

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			conDb.disconectDatabase();
		}

	}

	/*@Override*/
	public void update(Position pos) {
		Connection con = conDb.getConnection();
		String command = "Update position set name=?,nrPersons=?,idUser=?,idPositionDescription=?,idPositionStatus=? where idPosition=?";
		PreparedStatement stmt;
		try {
			stmt = con.prepareStatement(command);
			stmt.setString(1, pos.getName());
			stmt.setInt(2, pos.getNrPersons());
			stmt.setInt(3, pos.getUser().getIdUser());
			stmt.setInt(4, pos.getPositionDescription().getIdpositiondescription());
			stmt.setInt(5, pos.getPositionStatus().getIdPositionStatus());
			stmt.setInt(6, pos.getIdPosition());
			stmt.executeQuery();

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			conDb.disconectDatabase();
		}

	}

}
